package org.hao.samples.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelDemo {

	public static void main(String[] args) {
		String path = "D://java01//workspace//.metadata//.plugins//org.eclipse.wst.server.core//tmp1//wtpwebapps//teacher//upload//student.xlsx";
		try {
			//创建excel文件的输入对象
			InputStream is = new FileInputStream(path);
			//xssworkbook 就代表一个excel文件
			//创建其对象，就打开这个 excel文件
			XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
			// read the sheet
			for(int numSheet = 0;numSheet < xssfWorkbook.getNumberOfSheets();numSheet++){
				//xssfsheet 代表excel文件中的一张表格
				//我们通过getsheetAT(0)指定表格索引来获取对应的表格
				//注意表格索引从0开始
					XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
					if(xssfSheet == null){
						continue;
					}
					//read the row
					//开始循环表格数据，表格的行索引从0开始
					//test.xlsx第一行是标题行，我们从第二行开始，对应的行索引是1
					for(int rowNum = 1;rowNum <= xssfSheet.getLastRowNum();rowNum++){
						XSSFRow xssfRow = xssfSheet.getRow(rowNum);
						if(xssfRow != null){
							XSSFCell userName = xssfRow.getCell(0);
							XSSFCell password = xssfRow.getCell(1);
							XSSFCell age = xssfRow.getCell(2);
							XSSFCell sex = xssfRow.getCell(3);
							XSSFCell email = xssfRow.getCell(4);
							
							System.out.print(getValue(userName)+"  ");
							System.out.print(getValue(password)+"  ");
							System.out.print(new Integer(getValue(age))+"  ");
							System.out.print(getValue(sex)+"  ");							
							System.out.println(email);
						}
					}
			}
			
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
	}

	@SuppressWarnings("ststic-access")
	private static String getValue(XSSFCell xssfRow){
		if(xssfRow == null){
			return "";
		}
		if(xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN){
			return String.valueOf(xssfRow.getBooleanCellValue());
		} else if(xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC){
			DecimalFormat df = new DecimalFormat("0");
			return df.format(xssfRow.getNumericCellValue());
		} else {
			return String.valueOf(xssfRow.getNumericCellValue());
		}
	}
}
